import tkinter as tk
from tkinter import ttk, messagebox, simpledialog
import mysql.connector
from mysql.connector import Error
from datetime import datetime


class DataViewer:
    def __init__(self, master):
        self.master = master
        self.master.title("监狱人员管理系统")
        self.master.geometry("1000x700")  # 扩大窗口以适应新功能

        # MySQL连接配置
        self.db_config = {
            'host': "127.0.0.1",
            'user': "root",
            'password': "831114399x",
            'database': "person_db"
        }

        self.conn = None
        self.current_filter = ""  # 添加筛选状态追踪
        self.all_data = []  # 存储所有数据
        self.current_page = 1
        self.total_pages = 0
        self.fields = ['id', 'room_number', 'name', 'join_date']
        self.field_names = ['ID', '房间号', '姓名', '入所日期']
        self.selected_id = None  # 存储当前选中行ID

        # 创建数据库连接
        self.connect_to_db()

        # 创建界面
        self.create_widgets()
        self.load_data()

        # 添加退出时清理资源的处理
        self.master.protocol("WM_DELETE_WINDOW", self.on_close)

    def connect_to_db(self):
        """连接到MySQL数据库"""
        try:
            self.conn = mysql.connector.connect(**self.db_config)
            print(f"✅ 成功连接到MySQL数据库")
            return True
        except Error as e:
            messagebox.showerror("数据库连接错误", f"连接失败:\n{str(e)}")
            self.master.destroy()
            return False

    def print_header_info(self):
        """打印数据库表头信息"""
        if not self.conn or not self.conn.is_connected():
            print("❌ 数据库未连接")
            return

        try:
            cursor = self.conn.cursor()

            # 获取表结构
            cursor.execute("DESCRIBE persons")
            table_columns = cursor.fetchall()

            # 获取前3条记录
            cursor.execute("SELECT * FROM persons LIMIT 3")
            sample_data = cursor.fetchall()

            cursor.close()

            print("=== MySQL数据库表信息 ===")
            print(f"数据库: {self.db_config['database']}")
            print(f"表: persons")
            print("\n表结构:")
            for column in table_columns:
                print(f"  字段: {column[0]}, 类型: {column[1]}")

            print("\n示例数据:")
            for i, row in enumerate(sample_data, 1):
                print(f"记录 {i}: {dict(zip(self.fields, row))}")

            print("========================")

        except Error as e:
            print(f"❌ 获取表信息失败: {str(e)}")

    def create_widgets(self):
        """创建界面组件"""
        # 顶部操作区域
        self.top_frame = ttk.Frame(self.master)
        self.top_frame.pack(pady=10, padx=20, fill=tk.X)

        # 房间筛选组件
        ttk.Label(self.top_frame, text="房间号筛选:").pack(side=tk.LEFT, padx=(0, 5))
        self.filter_entry = ttk.Entry(self.top_frame, width=15)
        self.filter_entry.pack(side=tk.LEFT, padx=5)
        self.filter_button = ttk.Button(self.top_frame, text="筛选", command=self.filter_by_room)
        self.filter_button.pack(side=tk.LEFT, padx=(0, 10))

        # 重置筛选按钮
        self.reset_button = ttk.Button(self.top_frame, text="重置筛选", command=self.reset_filter)
        self.reset_button.pack(side=tk.LEFT, padx=5)

        # 数据操作按钮
        ttk.Separator(self.top_frame, orient=tk.VERTICAL).pack(side=tk.LEFT, padx=15, fill=tk.Y)
        self.add_btn = ttk.Button(self.top_frame, text="添加人员", command=self.add_person)
        self.add_btn.pack(side=tk.LEFT, padx=5)
        self.edit_btn = ttk.Button(self.top_frame, text="编辑人员", command=self.edit_person)
        self.edit_btn.pack(side=tk.LEFT, padx=5)
        self.delete_btn = ttk.Button(self.top_frame, text="删除人员", command=self.delete_person)
        self.delete_btn.pack(side=tk.LEFT, padx=5)

        # 表格框架
        self.tree_frame = ttk.Frame(self.master)
        self.tree_frame.pack(pady=10, padx=20, fill=tk.BOTH, expand=True)

        # 表格设置
        self.tree = ttk.Treeview(self.tree_frame, columns=self.field_names, show='headings', selectmode='browse')

        # 表头和列宽设置
        for col in self.field_names:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=100 if col == 'ID' else 150, anchor=tk.CENTER)

        # 绑定选择事件
        self.tree.bind("<<TreeviewSelect>>", self.on_row_select)

        # 滚动条
        vsb = ttk.Scrollbar(self.tree_frame, orient="vertical", command=self.tree.yview)
        vsb.pack(side=tk.RIGHT, fill=tk.Y)
        self.tree.configure(yscrollcommand=vsb.set)
        self.tree.pack(fill=tk.BOTH, expand=True)

        # 分页控制
        self.control_frame = ttk.Frame(self.master)
        self.control_frame.pack(pady=10)

        self.prev_btn = ttk.Button(self.control_frame, text="上一页", command=self.prev_page)
        self.prev_btn.pack(side=tk.LEFT, padx=5)

        self.page_label = ttk.Label(self.control_frame, text="第0页/共0页")
        self.page_label.pack(side=tk.LEFT, padx=5)

        self.next_btn = ttk.Button(self.control_frame, text="下一页", command=self.next_page)
        self.next_btn.pack(side=tk.LEFT, padx=5)

        # 添加刷新按钮
        self.refresh_btn = ttk.Button(self.control_frame, text="刷新数据", command=self.refresh_data)
        self.refresh_btn.pack(side=tk.LEFT, padx=5)

        # 添加状态栏
        self.status_bar = ttk.Label(self.master, text="就绪", relief=tk.SUNKEN, anchor=tk.W)
        self.status_bar.pack(side=tk.BOTTOM, fill=tk.X)

    def on_row_select(self, event):
        """处理行选择事件"""
        selected = self.tree.selection()
        if selected:
            # 获取选中行的ID值（第一列）
            self.selected_id = self.tree.item(selected[0], 'values')[0]
        else:
            self.selected_id = None

    def filter_by_room(self):
        """根据房间号筛选数据"""
        room_number = self.filter_entry.get().strip()
        if not room_number:
            messagebox.showwarning("输入错误", "请输入房间号")
            return

        self.current_filter = room_number
        self.status_bar.config(text=f"⏳ 正在筛选房间号: {room_number}...")
        self.master.update()
        self.load_data()

    def reset_filter(self):
        """重置筛选条件"""
        self.filter_entry.delete(0, tk.END)
        self.current_filter = ""
        self.load_data()

    def load_data(self):
        """从数据库加载数据"""
        if not self.connection_check():
            return

        try:
            cursor = self.conn.cursor()

            # 动态构建SQL查询（是否包含筛选条件）
            base_query = "SELECT id, room_number, name, join_date FROM persons"
            if self.current_filter:
                query = f"{base_query} WHERE room_number = %s ORDER BY id"
                cursor.execute(query, (self.current_filter,))
            else:
                query = f"{base_query} ORDER BY id"
                cursor.execute(query)

            self.all_data = cursor.fetchall()
            cursor.close()

            # 更新分页信息
            self.current_page = 1
            self.total_pages = max(1, (len(self.all_data) + 19) // 20)
            self.update_display()

            self.status_bar.config(
                text=f"✅ 已加载 {len(self.all_data)} 条记录 | {'已筛选' if self.current_filter else '全部数据'}")

        except Error as e:
            self.status_bar.config(text=f"❌ 数据库错误: {str(e)}")

    def create_input_dialog(self, title, initial_values=None):
        """创建人员信息输入对话框"""
        dialog = tk.Toplevel(self.master)
        dialog.title(title)
        dialog.geometry("400x300")
        dialog.transient(self.master)
        dialog.grab_set()

        fields = []

        # 姓名输入
        ttk.Label(dialog, text="姓名:").grid(row=0, column=0, padx=10, pady=5, sticky=tk.E)
        name_var = tk.StringVar(value=initial_values[1] if initial_values else "")
        name_entry = ttk.Entry(dialog, textvariable=name_var)
        name_entry.grid(row=0, column=1, padx=10, pady=5, sticky=tk.W)
        fields.append(("name", name_var))

        # 房间号输入
        ttk.Label(dialog, text="房间号:").grid(row=1, column=0, padx=10, pady=5, sticky=tk.E)
        room_var = tk.StringVar(value=initial_values[0] if initial_values else "")
        room_entry = ttk.Entry(dialog, textvariable=room_var)
        room_entry.grid(row=1, column=1, padx=10, pady=5, sticky=tk.W)
        fields.append(("room_number", room_var))

        # 入所日期输入
        ttk.Label(dialog, text="入所日期 (YYYY-MM-DD):").grid(row=2, column=0, padx=10, pady=5, sticky=tk.E)
        date_var = tk.StringVar(value=initial_values[2] if initial_values else "")
        date_entry = ttk.Entry(dialog, textvariable=date_var)
        date_entry.grid(row=2, column=1, padx=10, pady=5, sticky=tk.W)
        fields.append(("join_date", date_var))

        # 确认按钮
        def submit():
            dialog.result = {field[0]: field[1].get().strip() for field in fields}
            dialog.destroy()

        ttk.Button(dialog, text="确认", command=submit).grid(row=3, column=0, columnspan=2, pady=15)

        dialog.wait_window(dialog)
        return getattr(dialog, 'result', None)

    def add_person(self):
        """添加新人员"""
        data = self.create_input_dialog("添加新人员")
        if not data:
            return

        if not all(data.values()):
            messagebox.showerror("输入错误", "所有字段都必须填写")
            return

        try:
            datetime.strptime(data['join_date'], '%Y-%m-%d')  # 验证日期格式
        except ValueError:
            messagebox.showerror("格式错误", "日期格式必须为YYYY-MM-DD")
            return

        try:
            cursor = self.conn.cursor()
            query = """INSERT INTO persons (room_number, name, join_date)
                       VALUES (%s, %s, %s)"""
            cursor.execute(query, (data['room_number'], data['name'], data['join_date']))
            self.conn.commit()
            cursor.close()

            messagebox.showinfo("成功", "人员添加成功")
            self.load_data()  # 刷新数据

        except Error as e:
            self.conn.rollback()
            messagebox.showerror("数据库错误", f"添加失败:\n{str(e)}")

    def edit_person(self):
        """编辑现有人员"""
        if not self.selected_id:
            messagebox.showwarning("未选择", "请先在表格中选择要编辑的人员")
            return

        # 获取当前选中数据
        selected_item = None
        for item in self.all_data:
            if str(item[0]) == str(self.selected_id):
                selected_item = item
                break

        if not selected_item:
            return

        # 显示编辑对话框
        initial_values = (
            selected_item[1],  # room_number
            selected_item[2],  # name
            selected_item[3].strftime('%Y-%m-%d') if selected_item[3] else ""  # join_date
        )

        data = self.create_input_dialog("编辑人员信息", initial_values)
        if not data:
            return

        try:
            cursor = self.conn.cursor()
            query = """UPDATE persons
                       SET room_number = %s, name = %s, join_date = %s
                       WHERE id = %s"""
            cursor.execute(query, (
                data['room_number'],
                data['name'],
                data['join_date'],
                self.selected_id
            ))
            self.conn.commit()
            cursor.close()

            messagebox.showinfo("成功", "人员信息已更新")
            self.load_data()  # 刷新数据

        except Error as e:
            self.conn.rollback()
            messagebox.showerror("数据库错误", f"更新失败:\n{str(e)}")

    def delete_person(self):
        """删除选中人员"""
        if not self.selected_id:
            messagebox.showwarning("未选择", "请先选择要删除的人员")
            return

        if not messagebox.askyesno("确认删除", f"确定要删除ID为 {self.selected_id} 的人员吗？"):
            return

        try:
            cursor = self.conn.cursor()
            query = "DELETE FROM persons WHERE id = %s"
            cursor.execute(query, (self.selected_id,))
            self.conn.commit()
            cursor.close()

            messagebox.showinfo("成功", "人员已删除")
            self.selected_id = None
            self.load_data()  # 刷新数据

        except Error as e:
            self.conn.rollback()
            messagebox.showerror("数据库错误", f"删除失败:\n{str(e)}")

    def prev_page(self):
        """显示上一页"""
        if self.current_page > 1:
            self.current_page -= 1
            self.update_display()

    def next_page(self):
        """显示下一页"""
        if self.current_page < self.total_pages:
            self.current_page += 1
            self.update_display()

    def refresh_data(self):
        """刷新数据"""
        self.status_bar.config(text="⏳ 正在刷新数据...")
        self.master.update()  # 立即更新UI
        self.load_data()

    def update_display(self):
        """更新表格显示"""
        # 清空现有数据
        for row in self.tree.get_children():
            self.tree.delete(row)

        # 如果没有数据
        if not self.all_data:
            self.page_label.config(text="没有数据")
            self.prev_btn['state'] = 'disabled'
            self.next_btn['state'] = 'disabled'
            return

        # 计算显示范围
        start = (self.current_page - 1) * 20
        end = min(start + 20, len(self.all_data))
        page_data = self.all_data[start:end]

        # 插入新数据
        for item in page_data:
            self.tree.insert('', tk.END, values=(
                item[0],  # id
                item[1],  # room_number
                item[2],  # name
                item[3].strftime('%Y-%m-%d') if item[3] else ''  # join_date (转换为字符串)
            ))

        # 更新分页信息
        self.page_label.config(text=f"第{self.current_page}页/共{self.total_pages}页")

        # 控制按钮状态
        self.prev_btn['state'] = 'normal' if self.current_page > 1 else 'disabled'
        self.next_btn['state'] = 'normal' if self.current_page < self.total_pages else 'disabled'

    def on_close(self):
        """关闭窗口时的清理工作"""
        if self.conn and self.conn.is_connected():
            self.conn.close()
            print("✅ 已关闭数据库连接")
        self.master.destroy()

    def connection_check(self):
        """检查并确保数据库连接有效"""
        if not self.conn or not self.conn.is_connected():
            if not self.connect_to_db():
                return False
        return True


if __name__ == "__main__":
    root = tk.Tk()
    app = DataViewer(root)
    root.mainloop()
